﻿using System;
using System.Collections.Generic;
using System.Data;
using System.Data.SqlClient;
using System.Linq;
using System.Text;
using System.Windows.Forms;
using Worktool.CODE_Chris.SQL_DATA;

namespace Worktool.CODE_Chris.DataSourceHelpers
{
    public class BaufortschrittHelper
    {
        private Baufortschritt parent;
        private GeneralHelper helper;
        private SQL sql;

        private DataGridView gridView1, gridView2;

        private SqlDataAdapter sqlDataAdapter1 = new SqlDataAdapter();
        private SqlDataAdapter sqlDataAdapter2 = new SqlDataAdapter();

        private BindingSource bindingSource1 = new BindingSource();
        private BindingSource bindingSource2 = new BindingSource();

        private DataTable dataTable1, dataTable2;

        private String select1, select2;
        private String update1, update2;

        private Boolean isPaste = false;
        private int countPasteError = 0;

        public BaufortschrittHelper(DataGridView gridView1, DataGridView gridView2, Baufortschritt parent)
        {
            this.parent = parent;
            this.gridView1 = gridView1;
            this.gridView2 = gridView2;
            init();
        }

        private void init()
        {
            sql = new SQL();
            helper = new GeneralHelper();

            populateSQLStrings();
        }

        #region Methoden für gridView1 (Hauptmaske)
        public void getData1()
        {
            try
            {
                gridView1.DataSource = bindingSource1;

                sqlDataAdapter1 = new SqlDataAdapter(select1, Login.connection);
                sqlDataAdapter1.UpdateCommand = new SqlCommand(update1, new SqlConnection(Login.connection));

                //Neu zu schreibende Parameter
                sqlDataAdapter1.UpdateCommand.Parameters.Add("@foundationInstalled", SqlDbType.NVarChar, 50, "foundationInstalled");
                sqlDataAdapter1.UpdateCommand.Parameters.Add("@deviceInstalled", SqlDbType.NVarChar, 50, "deviceInstalled");
                sqlDataAdapter1.UpdateCommand.Parameters.Add("@remarks", SqlDbType.NVarChar, -1, "remarks");
                sqlDataAdapter1.UpdateCommand.Parameters.Add("@updateUser", SqlDbType.NVarChar, 50, "updateUser");
                sqlDataAdapter1.UpdateCommand.Parameters.Add("@updateDate", SqlDbType.Date, 10, "updateDate");

                //Daten aus der Originalquelle
                SqlParameter parameter = sqlDataAdapter1.UpdateCommand.Parameters.Add("@Original_ID", SqlDbType.Int, 50, "ID");
                parameter.SourceVersion = DataRowVersion.Original;
                parameter = sqlDataAdapter1.UpdateCommand.Parameters.Add("@Original_foundationInstalled", SqlDbType.NVarChar, 50, "foundationInstalled");
                parameter.SourceVersion = DataRowVersion.Original;
                parameter = sqlDataAdapter1.UpdateCommand.Parameters.Add("@Original_deviceInstalled", SqlDbType.NVarChar, 50, "deviceInstalled");
                parameter.SourceVersion = DataRowVersion.Original;
                parameter = sqlDataAdapter1.UpdateCommand.Parameters.Add("@Original_remarks", SqlDbType.NVarChar, -1, "remarks");
                parameter.SourceVersion = DataRowVersion.Original;
                parameter = sqlDataAdapter1.UpdateCommand.Parameters.Add("@Original_updateUser", SqlDbType.NVarChar, 50, "updateUser");
                parameter.SourceVersion = DataRowVersion.Original;
                parameter = sqlDataAdapter1.UpdateCommand.Parameters.Add("@Original_updateDate", SqlDbType.Date, 10, "updateDate");
                parameter.SourceVersion = DataRowVersion.Original;

                //Check auf Null-Werte
                parameter = sqlDataAdapter1.UpdateCommand.Parameters.Add("@IsNull_foundationInstalled", SqlDbType.Int, 50, "foundationInstalled");
                parameter.SourceVersion = DataRowVersion.Original;
                parameter.SourceColumnNullMapping = true;
                parameter = sqlDataAdapter1.UpdateCommand.Parameters.Add("@IsNull_deviceInstalled", SqlDbType.Int, 10, "deviceInstalled");
                parameter.SourceVersion = DataRowVersion.Original;
                parameter.SourceColumnNullMapping = true;
                parameter = sqlDataAdapter1.UpdateCommand.Parameters.Add("@IsNull_remarks", SqlDbType.Int, 50, "remarks");
                parameter.SourceVersion = DataRowVersion.Original;
                parameter.SourceColumnNullMapping = true;
                parameter = sqlDataAdapter1.UpdateCommand.Parameters.Add("@IsNull_updateUser", SqlDbType.Int, 10, "updateUser");
                parameter.SourceVersion = DataRowVersion.Original;
                parameter.SourceColumnNullMapping = true;
                parameter = sqlDataAdapter1.UpdateCommand.Parameters.Add("@IsNull_updateDate", SqlDbType.Int, 50, "updateDate");
                parameter.SourceVersion = DataRowVersion.Original;
                parameter.SourceColumnNullMapping = true;

                SqlCommandBuilder commandBuilder = new SqlCommandBuilder(sqlDataAdapter1);

                // Populate a new data table and bind it to the BindingSource.
                dataTable1 = new DataTable();
                dataTable1.Locale = System.Globalization.CultureInfo.InvariantCulture;
                sqlDataAdapter1.Fill(dataTable1);
                bindingSource1.DataSource = dataTable1;

                gridView1.AutoGenerateColumns = false;

                //Combobox Spalten einbauen
                gridView1.Columns.Remove("foundationInstalled");
                gridView1.Columns.Remove("deviceInstalled");
                addComboBoxColumns1();

                //Zellennachbearbeitung
                setHiddenColumns1();
                setDisabledColumns1();

                //Eventhandler setzen
                gridView1.CellFormatting -= new DataGridViewCellFormattingEventHandler(this.gridView1_CellFormatting);
                gridView1.CellEndEdit -= new DataGridViewCellEventHandler(this.gridView1_CellEndEdit);
                gridView1.CellMouseDown -= new DataGridViewCellMouseEventHandler(this.gridView1_CellMouseDown);
                gridView1.CellClick -= new DataGridViewCellEventHandler(this.gridView1_CellClick);
                gridView1.CellMouseClick -= new DataGridViewCellMouseEventHandler(this.gridView1_CellMouseClick);
                gridView1.RowEnter -= new DataGridViewCellEventHandler(this.gridView1_RowEnter);
                gridView1.CellValueChanged -= new DataGridViewCellEventHandler(this.gridView1_CellValueChanged);

                gridView1.CellFormatting += new DataGridViewCellFormattingEventHandler(this.gridView1_CellFormatting);
                gridView1.CellEndEdit += new DataGridViewCellEventHandler(this.gridView1_CellEndEdit);
                gridView1.CellMouseDown += new DataGridViewCellMouseEventHandler(this.gridView1_CellMouseDown);
                gridView1.CellClick += new DataGridViewCellEventHandler(this.gridView1_CellClick);
                gridView1.CellMouseClick += new DataGridViewCellMouseEventHandler(this.gridView1_CellMouseClick);
                gridView1.RowEnter += new DataGridViewCellEventHandler(this.gridView1_RowEnter);
                gridView1.CellValueChanged += new DataGridViewCellEventHandler(this.gridView1_CellValueChanged);
            }
            catch (Exception e)
            {
                sql.log("FEHLER", e, this.ToString() + ":" + System.Reflection.MethodBase.GetCurrentMethod().Name);
            }
        }

        public void addComboBoxColumns1()
        {
            DataGridViewComboBoxColumn foundationInstalled;
            foundationInstalled = helper.createComboBoxColumn("foundationInstalled");
            foundationInstalled.HeaderText = "foundationInstalled";
            foundationInstalled.ValueMember = "foundationInstalled";
            foundationInstalled.DisplayMember = "foundationInstalled";
            foundationInstalled.Name = "foundationInstalled";
            foundationInstalled.MaxDropDownItems = 50;
            foundationInstalled.SortMode = DataGridViewColumnSortMode.Automatic;
            foundationInstalled.Items.Add("");
            foundationInstalled.Items.Add("YES");
            foundationInstalled.Items.Add("NO");
            foundationInstalled.Items.Add("N/A");
            gridView1.Columns.Insert(0, foundationInstalled);
            gridView1.Columns["foundationInstalled"].DisplayIndex = 7;

            DataGridViewComboBoxColumn deviceInstalled;
            deviceInstalled = helper.createComboBoxColumn("deviceInstalled");
            deviceInstalled.HeaderText = "deviceInstalled";
            deviceInstalled.ValueMember = "deviceInstalled";
            deviceInstalled.DisplayMember = "deviceInstalled";
            deviceInstalled.Name = "deviceInstalled";
            deviceInstalled.MaxDropDownItems = 50;
            deviceInstalled.SortMode = DataGridViewColumnSortMode.Automatic;
            deviceInstalled.Items.Add("");
            deviceInstalled.Items.Add("YES");
            deviceInstalled.Items.Add("NO");
            deviceInstalled.Items.Add("N/A");
            gridView1.Columns.Insert(0, deviceInstalled);
            gridView1.Columns["deviceInstalled"].DisplayIndex = 8;
        }

        private void gridView1_RowEnter(object sender, DataGridViewCellEventArgs e)
        {
            if (gridView1["Positions_ID", e.RowIndex].Value.ToString() != "")
            {
                try
                {
                    if (getTable2() != null) getTable2().DefaultView.RowFilter = string.Format("CableFrom2 LIKE '{0}' OR CableTo2 LIKE '{0}'", gridView1["Positions_ID", e.RowIndex].Value.ToString());
                }
                catch (Exception ex)
                { }
            }
            else
            {
                if (getTable2() != null) getTable2().DefaultView.RowFilter = string.Format("CableFrom2 LIKE 'ABCDEFGHIJKLMN'");
            }
        }

        private void gridView1_CellMouseClick(object sender, DataGridViewCellMouseEventArgs e)
        {
            if (e.Button == MouseButtons.Right)
            {
                ContextMenu m = new ContextMenu();

                MenuItem pasteY = new MenuItem("Pase \"YES\"");
                pasteY.Click += new System.EventHandler(this.pasteYES1_Click);
                m.MenuItems.Add(pasteY);

                MenuItem pasteN = new MenuItem("Paste \"NO\"");
                pasteN.Click += new System.EventHandler(this.pasteNO1_Click);
                m.MenuItems.Add(pasteN);

                MenuItem pasteNA = new MenuItem("Paste \"N/A\"");
                pasteNA.Click += new System.EventHandler(this.pasteNA1_Click);
                m.MenuItems.Add(pasteNA);

                MenuItem clear = new MenuItem("Clear");
                clear.Click += new System.EventHandler(this.clear1_Click);
                m.MenuItems.Add(clear);

                var relativeMousePosition = gridView1.PointToClient(Cursor.Position);
                m.Show(gridView1, relativeMousePosition);
            }
        }

        private void pasteYES1_Click(object sender, EventArgs e)
        {
            //Für Korrekte Behandlung der DBConcurrencyException
            isPaste = true;

            List<int> pasteZeilen = new List<int> { };
            string pasteColumn = "";
            if (gridView1.SelectedCells.Count != 0) pasteColumn = gridView1.SelectedCells[0].OwningColumn.Name;

            for (int i = 0; i < gridView1.SelectedCells.Count; i++)
            {
                pasteZeilen.Add(gridView1.SelectedCells[i].OwningRow.Index);
            }

            for (int i = 0; i < pasteZeilen.Count; i++)
            {
                if (!gridView1.SelectedCells[i].OwningColumn.ReadOnly)
                {
                    try
                    {
                        gridView1.Rows[pasteZeilen[i]].Cells[pasteColumn].Value = "YES";
                        gridView1.Rows[pasteZeilen[i]].Cells["updateDate"].Value = DateTime.Today;
                        gridView1.Rows[pasteZeilen[i]].Cells["updateUser"].Value = Login.user;

                        // Zeile zurück in die DB schreiben
                        gridView1_CellEndEdit(null, null);
                    }
                    catch (Exception ex)
                    {

                    }
                }
            }
            if (countPasteError > 0)
            {
                if (MessageBox.Show("Der Datensatz wurde in der Zwischenzeit auf dem Server bearbeitet. Es wurden nur die Datensätze aktualisiert, die nicht im Hintergrund bearbeitet wurden. Alle Datensätze werden nun neu geladen.", "Fehler", MessageBoxButtons.OK) == DialogResult.OK)
                {
                    parent.reloadAllData();
                }
            }

            countPasteError = 0;
            isPaste = false;
        }

        private void pasteNO1_Click(object sender, EventArgs e)
        {
            //Für Korrekte Behandlung der DBConcurrencyException
            isPaste = true;

            List<int> pasteZeilen = new List<int> { };
            string pasteColumn = "";
            if (gridView1.SelectedCells.Count != 0) pasteColumn = gridView1.SelectedCells[0].OwningColumn.Name;

            for (int i = 0; i < gridView1.SelectedCells.Count; i++)
            {
                pasteZeilen.Add(gridView1.SelectedCells[i].OwningRow.Index);
            }

            for (int i = 0; i < pasteZeilen.Count; i++)
            {
                if (!gridView1.SelectedCells[i].OwningColumn.ReadOnly)
                {
                    try
                    {
                        gridView1.Rows[pasteZeilen[i]].Cells[pasteColumn].Value = "NO";
                        gridView1.Rows[pasteZeilen[i]].Cells["updateDate"].Value = DateTime.Today;
                        gridView1.Rows[pasteZeilen[i]].Cells["updateUser"].Value = Login.user;

                        // Zeile zurück in die DB schreiben
                        gridView1_CellEndEdit(null, null);
                    }
                    catch (Exception ex)
                    {

                    }
                }
            }
            if (countPasteError > 0)
            {
                if (MessageBox.Show("Der Datensatz wurde in der Zwischenzeit auf dem Server bearbeitet. Es wurden nur die Datensätze aktualisiert, die nicht im Hintergrund bearbeitet wurden. Alle Datensätze werden nun neu geladen.", "Fehler", MessageBoxButtons.OK) == DialogResult.OK)
                {
                    parent.reloadAllData();
                }
            }

            countPasteError = 0;
            isPaste = false;
        }

        private void pasteNA1_Click(object sender, EventArgs e)
        {
            //Für Korrekte Behandlung der DBConcurrencyException
            isPaste = true;

            List<int> pasteZeilen = new List<int> { };
            string pasteColumn = "";
            if (gridView1.SelectedCells.Count != 0) pasteColumn = gridView1.SelectedCells[0].OwningColumn.Name;

            for (int i = 0; i < gridView1.SelectedCells.Count; i++)
            {
                pasteZeilen.Add(gridView1.SelectedCells[i].OwningRow.Index);
            }

            for (int i = 0; i < pasteZeilen.Count; i++)
            {
                if (!gridView1.SelectedCells[i].OwningColumn.ReadOnly)
                {
                    try
                    {
                        gridView1.Rows[pasteZeilen[i]].Cells[pasteColumn].Value = "N/A";
                        gridView1.Rows[pasteZeilen[i]].Cells["updateDate"].Value = DateTime.Today;
                        gridView1.Rows[pasteZeilen[i]].Cells["updateUser"].Value = Login.user;

                        // Zeile zurück in die DB schreiben
                        gridView1_CellEndEdit(null, null);
                    }
                    catch (Exception ex)
                    {

                    }
                }
            }
            if (countPasteError > 0)
            {
                if (MessageBox.Show("Der Datensatz wurde in der Zwischenzeit auf dem Server bearbeitet. Es wurden nur die Datensätze aktualisiert, die nicht im Hintergrund bearbeitet wurden. Alle Datensätze werden nun neu geladen.", "Fehler", MessageBoxButtons.OK) == DialogResult.OK)
                {
                    parent.reloadAllData();
                }
            }

            countPasteError = 0;
            isPaste = false;
        }

        private void clear1_Click(object sender, EventArgs e)
        {
            //Für Korrekte Behandlung der DBConcurrencyException
            isPaste = true;

            List<int> deleteZeilen = new List<int> { };
            string deleteColumn = "";
            if (gridView1.SelectedCells.Count != 0) deleteColumn = gridView1.SelectedCells[0].OwningColumn.Name;

            for (int i = 0; i < gridView1.SelectedCells.Count; i++)
            {
                deleteZeilen.Add(gridView1.SelectedCells[i].OwningRow.Index);
            }

            if (gridView1.SelectedCells.Count != 0)
            {
                for (int i = 0; i < gridView1.SelectedCells.Count; i++)
                {
                    if (!gridView1.SelectedCells[i].OwningColumn.ReadOnly)
                    {
                        try
                        {
                            if (gridView1.SelectedCells[i].ValueType == typeof(System.DateTime)) gridView1.SelectedCells[i].Value = DBNull.Value;
                            if (gridView1.SelectedCells[i].ValueType == typeof(int)) gridView1.SelectedCells[i].Value = 0;
                            if (gridView1.SelectedCells[i].ValueType == typeof(string)) gridView1.SelectedCells[i].Value = String.Empty;
                            if (gridView1.SelectedCells[i].ValueType == typeof(Boolean)) gridView1.SelectedCells[i].Value = Boolean.FalseString;

                            if (gridView1.SelectedCells[i].ValueType == typeof(System.DateTime) || gridView1.SelectedCells[i].ValueType == typeof(int) || gridView1.SelectedCells[i].ValueType == typeof(string) || gridView1.SelectedCells[i].ValueType == typeof(Boolean))
                            {
                                // Zeile zurück in die DB schreiben
                                gridView1_CellEndEdit(null, null);
                            }
                        }
                        catch (Exception ex)
                        {

                        }
                    }
                }

                if (countPasteError > 0)
                {
                    if (MessageBox.Show("Der Datensatz wurde in der Zwischenzeit auf dem Server bearbeitet. Es wurden nur die Datensätze aktualisiert, die nicht im Hintergrund bearbeitet wurden. Alle Datensätze werden nun neu geladen.", "Fehler", MessageBoxButtons.OK) == DialogResult.OK)
                    {
                        parent.reloadAllData();
                    }
                }
            }

            countPasteError = 0;
            isPaste = false;
        }

        private void gridView1_CellClick(object sender, DataGridViewCellEventArgs e)
        {
            //Dafür sorgen, dass Comboboxspalten sich sofort öffnen
            if (e.ColumnIndex == gridView1.Columns["foundationInstalled"].Index && e.RowIndex >= 0 && e.RowIndex <= gridView1.Rows.Count)
            {
                gridView1.CurrentCell = gridView1[e.ColumnIndex, e.RowIndex];
                gridView1.BeginEdit(false);
                ComboBox comboBox = gridView1.EditingControl as ComboBox;
                if (comboBox != null)
                {
                    comboBox.DroppedDown = true;
                }
            }

            if (e.ColumnIndex == gridView1.Columns["deviceInstalled"].Index && e.RowIndex >= 0 && e.RowIndex <= gridView1.Rows.Count)
            {
                gridView1.CurrentCell = gridView1[e.ColumnIndex, e.RowIndex];
                gridView1.BeginEdit(false);
                ComboBox comboBox = gridView1.EditingControl as ComboBox;
                if (comboBox != null)
                {
                    comboBox.DroppedDown = true;
                }
            }
        }

        private void gridView1_CellMouseDown(object sender, DataGridViewCellMouseEventArgs e)
        {
            if (e.Button == MouseButtons.Right)
            {
                if (e.RowIndex != -1 && e.ColumnIndex != -1)
                {
                    gridView1.CurrentCell = gridView1.Rows[e.RowIndex].Cells[e.ColumnIndex];
                }
                else
                {
                    gridView1.ClearSelection();
                }
            }
        }

        private void gridView1_CellValueChanged(object sender, DataGridViewCellEventArgs e)
        {
            gridView1.CurrentRow.Cells["updateDate"].Value = DateTime.Today;
            gridView1.CurrentRow.Cells["updateUser"].Value = Login.user;
        }

        private void gridView1_CellEndEdit(object sender, DataGridViewCellEventArgs e)
        {
            //Hier werden die Daten der editierten Zelle wieder in die Datenbank geschrieben
            gridView1.BindingContext[gridView1.DataSource].EndCurrentEdit();
            updateGridView1();
        }

        public Boolean updateGridView1()
        {
            try
            {
                sqlDataAdapter1.Update(dataTable1);
                return true;
            }
            catch (DBConcurrencyException e)
            {
                if (isPaste)
                {
                    countPasteError++;
                }
                else
                {
                    if (MessageBox.Show("Der Datensatz wurde in der Zwischenzeit auf dem Server bearbeitet. Die Datensätze werden neu geladen.", "Fehler", MessageBoxButtons.OK) == DialogResult.OK)
                    {
                        parent.reloadAllData();
                    }
                }

                sql.log("FEHLER", e, this.ToString() + ":" + System.Reflection.MethodBase.GetCurrentMethod().Name);
                return false;
            }
        }

        private void gridView1_CellFormatting(object sender, DataGridViewCellFormattingEventArgs e)
        {
            if (e.ColumnIndex != -1)
            {
                DataGridViewColumn col = gridView1.Columns[e.ColumnIndex];

                //Blaue Zellen
                if (col.Name == "Venue" && col.DefaultCellStyle.BackColor != helper.getColors()[0]) col.DefaultCellStyle.BackColor = helper.getColors()[0];
                if (col.Name == "Sparte" && col.DefaultCellStyle.BackColor != helper.getColors()[0]) col.DefaultCellStyle.BackColor = helper.getColors()[0];
                if (col.Name == "Manufacturer" && col.DefaultCellStyle.BackColor != helper.getColors()[0]) col.DefaultCellStyle.BackColor = helper.getColors()[0];
                if (col.Name == "Model" && col.DefaultCellStyle.BackColor != helper.getColors()[0]) col.DefaultCellStyle.BackColor = helper.getColors()[0];
                if (col.Name == "Description" && col.DefaultCellStyle.BackColor != helper.getColors()[0]) col.DefaultCellStyle.BackColor = helper.getColors()[0];
                if (col.Name == "JLM" && col.DefaultCellStyle.BackColor != helper.getColors()[0]) col.DefaultCellStyle.BackColor = helper.getColors()[0];
                if (col.Name == "Positions_ID" && col.DefaultCellStyle.BackColor != helper.getColors()[0]) col.DefaultCellStyle.BackColor = helper.getColors()[0];
                if (col.Name == "updateUser" && col.DefaultCellStyle.BackColor != helper.getColors()[0]) col.DefaultCellStyle.BackColor = helper.getColors()[0];
                if (col.Name == "updateDate" && col.DefaultCellStyle.BackColor != helper.getColors()[0]) col.DefaultCellStyle.BackColor = helper.getColors()[0];
            }
        }

        private void setDisabledColumns1()
        {
            try
            {
                gridView1.Columns["Venue"].ReadOnly = true;
                gridView1.Columns["Sparte"].ReadOnly = true;
                gridView1.Columns["Manufacturer"].ReadOnly = true;
                gridView1.Columns["Model"].ReadOnly = true;
                gridView1.Columns["Description"].ReadOnly = true;
                gridView1.Columns["JLM"].ReadOnly = true;
                gridView1.Columns["Positions_ID"].ReadOnly = true;
                gridView1.Columns["updateUser"].ReadOnly = true;
                gridView1.Columns["updateDate"].ReadOnly = true;
            }
            catch (Exception e)
            {

            }
        }

        private void setHiddenColumns1()
        {
            try
            {
                gridView1.Columns["cablesInstalled"].Visible = false;
                gridView1.Columns["cablesTerminated"].Visible = false;
                gridView1.Columns["cablesTested"].Visible = false;
                gridView1.Columns["ID"].Visible = false;
            }
            catch (Exception e)
            {

            }
        }

        public DataTable getTable1()
        {
            return dataTable1;
        }
        #endregion

        #region Methoden für gridView2 (Detailmaske)
        public void getData2()
        {
            try
            {
                gridView2.DataSource = bindingSource2;

                sqlDataAdapter2 = new SqlDataAdapter(select2, Login.connection);

                SqlCommandBuilder commandBuilder = new SqlCommandBuilder(sqlDataAdapter2);

                // Populate a new data table and bind it to the BindingSource.
                dataTable2 = new DataTable();
                dataTable2.Locale = System.Globalization.CultureInfo.InvariantCulture;
                sqlDataAdapter2.Fill(dataTable2);
                bindingSource2.DataSource = dataTable2;

                gridView2.AutoGenerateColumns = false;

                //Zellennachbearbeitung
                setHiddenColumns2();
                setDisabledColumns2();

                //Eventhandler setzen
                gridView2.CellFormatting -= new DataGridViewCellFormattingEventHandler(this.gridView2_CellFormatting);
                gridView2.CellEndEdit -= new DataGridViewCellEventHandler(this.gridView2_CellEndEdit);
                gridView2.CellMouseClick -= new DataGridViewCellMouseEventHandler(this.gridView2_CellMouseClick);
                gridView2.CellMouseDown -= new DataGridViewCellMouseEventHandler(this.gridView2_CellMouseDown);

                gridView2.CellFormatting += new DataGridViewCellFormattingEventHandler(this.gridView2_CellFormatting);
                gridView2.CellEndEdit += new DataGridViewCellEventHandler(this.gridView2_CellEndEdit);
                gridView2.CellMouseClick += new DataGridViewCellMouseEventHandler(this.gridView2_CellMouseClick);
                gridView2.CellMouseDown += new DataGridViewCellMouseEventHandler(this.gridView2_CellMouseDown);
            }
            catch (Exception e)
            {
                sql.log("FEHLER", e, this.ToString() + ":" + System.Reflection.MethodBase.GetCurrentMethod().Name);
            }
        }

        private void gridView2_CellMouseClick(object sender, DataGridViewCellMouseEventArgs e)
        {
            if (e.Button == MouseButtons.Right)
            {
                ContextMenu m = new ContextMenu();

                MenuItem set = new MenuItem("Set");
                set.Click += new System.EventHandler(this.set2_Click);
                m.MenuItems.Add(set);

                MenuItem reset = new MenuItem("Reset");
                reset.Click += new System.EventHandler(this.reset2_Click);
                m.MenuItems.Add(reset);

                var relativeMousePosition = gridView1.PointToClient(Cursor.Position);
                m.Show(gridView1, relativeMousePosition);
            }
        }

        private void set2_Click(object sender, EventArgs e)
        {
            //Für Korrekte Behandlung der DBConcurrencyException
            isPaste = true;

            List<int> pasteZeilen = new List<int> { };
            string pasteColumn = "";
            if (gridView2.SelectedCells.Count != 0) pasteColumn = gridView2.SelectedCells[0].OwningColumn.Name;

            for (int i = 0; i < gridView2.SelectedCells.Count; i++)
            {
                pasteZeilen.Add(gridView2.SelectedCells[i].OwningRow.Index);
            }

            for (int i = 0; i < pasteZeilen.Count; i++)
            {
                if (!gridView2.SelectedCells[i].OwningColumn.ReadOnly)
                {
                    if (gridView2.SelectedCells[i].ValueType == typeof(Boolean))
                    {
                        try
                        {
                            gridView2.SelectedCells[i].Value = true;

                            // Zeile zurück in die DB schreiben
                            gridView2_CellEndEdit(null, null);
                        }
                        catch (Exception ex)
                        {

                        }
                    }

                }
            }
            if (countPasteError > 0)
            {
                if (MessageBox.Show("Der Datensatz wurde in der Zwischenzeit auf dem Server bearbeitet. Es wurden nur die Datensätze aktualisiert, die nicht im Hintergrund bearbeitet wurden. Alle Datensätze werden nun neu geladen.", "Fehler", MessageBoxButtons.OK) == DialogResult.OK)
                {
                    parent.reloadAllData();
                }
            }

            countPasteError = 0;
            isPaste = false;
        }

        private void reset2_Click(object sender, EventArgs e)
        {
            //Für Korrekte Behandlung der DBConcurrencyException
            isPaste = true;

            List<int> pasteZeilen = new List<int> { };
            string pasteColumn = "";
            if (gridView2.SelectedCells.Count != 0) pasteColumn = gridView2.SelectedCells[0].OwningColumn.Name;

            for (int i = 0; i < gridView2.SelectedCells.Count; i++)
            {
                pasteZeilen.Add(gridView2.SelectedCells[i].OwningRow.Index);
            }

            for (int i = 0; i < pasteZeilen.Count; i++)
            {
                if (!gridView2.SelectedCells[i].OwningColumn.ReadOnly)
                {
                    if (gridView2.SelectedCells[i].ValueType == typeof(Boolean))
                    {
                        try
                        {
                            gridView2.SelectedCells[i].Value = false;

                            // Zeile zurück in die DB schreiben
                            gridView2_CellEndEdit(null, null);
                        }
                        catch (Exception ex)
                        {

                        }
                    }

                }
            }
            if (countPasteError > 0)
            {
                if (MessageBox.Show("Der Datensatz wurde in der Zwischenzeit auf dem Server bearbeitet. Es wurden nur die Datensätze aktualisiert, die nicht im Hintergrund bearbeitet wurden. Alle Datensätze werden nun neu geladen.", "Fehler", MessageBoxButtons.OK) == DialogResult.OK)
                {
                    parent.reloadAllData();
                }
            }

            countPasteError = 0;
            isPaste = false;
        }

        private void gridView2_CellMouseDown(object sender, DataGridViewCellMouseEventArgs e)
        {
            if (e.Button == MouseButtons.Right)
            {
                if (e.RowIndex != -1 && e.ColumnIndex != -1)
                {
                    gridView2.CurrentCell = gridView2.Rows[e.RowIndex].Cells[e.ColumnIndex];
                }
                else
                {
                    gridView2.ClearSelection();
                }
            }
        }

        private void gridView2_CellEndEdit(object sender, DataGridViewCellEventArgs e)
        {
            //Hier werden die Daten der editierten Zelle wieder in die Datenbank geschrieben
            gridView2.BindingContext[gridView2.DataSource].EndCurrentEdit();
            updateGridView2();
        }

        public Boolean updateGridView2()
        {
            try
            {
                sqlDataAdapter2.Update(dataTable2);
                return true;
            }
            catch (DBConcurrencyException e)
            {
                if (isPaste)
                {
                    countPasteError++;
                }
                else
                {
                    if (MessageBox.Show("Der Datensatz wurde in der Zwischenzeit auf dem Server bearbeitet. Die Datensätze werden neu geladen.", "Fehler", MessageBoxButtons.OK) == DialogResult.OK)
                    {
                        parent.reloadAllData();
                    }
                }

                sql.log("FEHLER", e, this.ToString() + ":" + System.Reflection.MethodBase.GetCurrentMethod().Name);
                return false;
            }
        }

        private void gridView2_CellFormatting(object sender, DataGridViewCellFormattingEventArgs e)
        {
            if (e.ColumnIndex != -1)
            {
                DataGridViewColumn col = gridView2.Columns[e.ColumnIndex];

                //Blaue Zellen
                if (col.Name == "CableFrom2" && col.DefaultCellStyle.BackColor != helper.getColors()[0]) col.DefaultCellStyle.BackColor = helper.getColors()[0];
                if (col.Name == "CableNumber" && col.DefaultCellStyle.BackColor != helper.getColors()[0]) col.DefaultCellStyle.BackColor = helper.getColors()[0];
                if (col.Name == "CableType" && col.DefaultCellStyle.BackColor != helper.getColors()[0]) col.DefaultCellStyle.BackColor = helper.getColors()[0];
                if (col.Name == "CableDesignation1" && col.DefaultCellStyle.BackColor != helper.getColors()[0]) col.DefaultCellStyle.BackColor = helper.getColors()[0];
                if (col.Name == "CableDesignation2" && col.DefaultCellStyle.BackColor != helper.getColors()[0]) col.DefaultCellStyle.BackColor = helper.getColors()[0];
                if (col.Name == "CableDesignation3" && col.DefaultCellStyle.BackColor != helper.getColors()[0]) col.DefaultCellStyle.BackColor = helper.getColors()[0];
                if (col.Name == "CableTo2" && col.DefaultCellStyle.BackColor != helper.getColors()[0]) col.DefaultCellStyle.BackColor = helper.getColors()[0];
            }
        }

        private void setDisabledColumns2()
        {
            try
            {
                gridView2.Columns["CableFrom2"].ReadOnly = true;
                gridView2.Columns["CableNumber"].ReadOnly = true;
                gridView2.Columns["CableType"].ReadOnly = true;
                gridView2.Columns["CableDesignation1"].ReadOnly = true;
                gridView2.Columns["CableDesignation2"].ReadOnly = true;
                gridView2.Columns["CableDesignation3"].ReadOnly = true;
                gridView2.Columns["CableTo2"].ReadOnly = true;
            }
            catch (Exception e)
            {

            }
        }

        private void setHiddenColumns2()
        {
            try
            {
                gridView2.Columns["ID"].Visible = false;
                gridView2.Columns["BGR"].Visible = false;
                gridView2.Columns["PLANT"].Visible = false;
                gridView2.Columns["POS"].Visible = false;
                gridView2.Columns["FU"].Visible = false;
                gridView2.Columns["OB"].Visible = false;
                gridView2.Columns["CableFrom1"].Visible = false;
                gridView2.Columns["RoomFrom"].Visible = false;
                gridView2.Columns["DeckFrom"].Visible = false;
                gridView2.Columns["FZFrom"].Visible = false;
                gridView2.Columns["WDFrom"].Visible = false;
                gridView2.Columns["CableTo1"].Visible = false;
                gridView2.Columns["RoomTo"].Visible = false;
                gridView2.Columns["DeckTo"].Visible = false;
                gridView2.Columns["FZTo"].Visible = false;
                gridView2.Columns["WDTo"].Visible = false;
                gridView2.Columns["Remarks"].Visible = false;
                gridView2.Columns["DM"].Visible = false;
                gridView2.Columns["Responsible"].Visible = false;
                gridView2.Columns["length"].Visible = false;
            }
            catch (Exception e)
            {

            }
        }

        public DataTable getTable2()
        {
            return dataTable2;
        }
        #endregion

        #region SQL-Befehle als Strings
        private void populateSQLStrings()
        {
            select1 = "SELECT Venue, Sparte, Manufacturer, Model, Description, JLM, Positions_ID, foundationInstalled, deviceInstalled, remarks, updateUser, updateDate, cablesInstalled, cablesTerminated, cablesTested, ID FROM vw_bauStatus";
            update1 = "UPDATE tbl_bauStatus SET "
	                    +"foundationInstalled = @foundationInstalled, "
	                    +"deviceInstalled = @deviceInstalled, "
	                    +"remarks = @remarks, "
	                    +"updateUser = @updateUser, "
	                    +"updateDate = @updateDate "
                        +"WHERE (ID = @Original_ID) "
                        +"AND (@IsNull_foundationInstalled = 1 AND foundationInstalled IS NULL OR foundationInstalled = @Original_foundationInstalled) "
                        +"AND (@IsNull_deviceInstalled = 1 AND deviceInstalled IS NULL OR deviceInstalled = @Original_deviceInstalled) "
                        +"AND (@IsNull_remarks = 1 AND remarks IS NULL OR remarks = @Original_remarks) "
                        +"AND (@IsNull_updateUser = 1 AND updateUser IS NULL OR updateUser = @Original_updateUser) "
                        +"AND (@IsNull_updateDate = 1 AND updateDate IS NULL OR updateDate = @Original_updateDate)";

            select2 = "SELECT isInstalledStart, isTerminatedStart, isTestedStart, CableFrom3, CableFrom2, CableNumber, CableType, CableDesignation1, CableDesignation2, CableDesignation3, CableTo2, CableTo3, isInstalledEnd, isTerminatedEnd, isTestedEnd, isDeleted, ID, BGR, PLANT, POS, FU, OB, CableFrom1, RoomFrom, DeckFrom, FZFrom, WDFrom, CableTo1, RoomTo, DeckTo, FZTo, WDTo, Remarks, DM, Responsible, length FROM tbl_CableList";
            update2 = "";
        }
        #endregion
    }
}
